﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;

namespace ClientTools.TestProject
{
    public class CleanVersionScript2
    {
        DCDataContext dc = new DCDataContext();
        private log4net.ILog log = log4net.LogManager.GetLogger(typeof(CleanVersionScript2));

        public void Go()
        {
            log4net.Config.XmlConfigurator.Configure();

            string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["ClientTools.TestProject.Properties.Settings.umbracoConnectionString"].ConnectionString;

            log.Info("Started...");
            List<umbracoNode> umbracoNodes = (from x in dc.umbracoNodes where x.nodeObjectType.Value.ToString() == "c66ba18e-eaf3-4cff-8a22-41b16d66a972" select x).ToList();
            log.Info(string.Format("Found {0} umbracoNodes", umbracoNodes.Count));

            foreach (umbracoNode node in umbracoNodes)
            {
                List<cmsDocument> documents = (from x in dc.cmsDocuments where x.nodeId == node.id select x).ToList();
                log.Info(string.Format("Found {0} documents for node {1}", documents.Count, node.id));
                cmsDocument publishedDocument = (from x in documents where x.published == true select x).FirstOrDefault();
                if (publishedDocument == null)
                {
                    log.Info(string.Format("No published document found for node {0}", node.id));
                    continue;
                }
                List<cmsContentVersion> versions = (from x in dc.cmsContentVersions where x.ContentId == node.id select x).ToList();
                log.Info(string.Format("Found {0} versions for node {1}", versions.Count, node.id));

                foreach (cmsDocument document in documents)
                {
                    if (document.published == true || document.releaseDate >= publishedDocument.releaseDate || document.newest) continue;
                    cmsDocument doc = (from x in dc.cmsDocuments where x.versionId == document.versionId && x.nodeId == document.nodeId select x).ToList().FirstOrDefault();
                    cmsContentVersion version = (from x in dc.cmsContentVersions where x.VersionId == document.versionId select x).ToList().FirstOrDefault();
                    using (SqlConnection connection = new SqlConnection(connstr))
                    {
                        connection.Open();
                        SqlTransaction transaction = connection.BeginTransaction();
                        SqlCommand commandVersion = connection.CreateCommand();
                        SqlCommand commandDocument = connection.CreateCommand();

                        int deletedProperties = 0;
                        List<cmsPropertyData> properties = (from x in new DCDataContext().cmsPropertyDatas where x.versionId == version.VersionId select x).ToList();
                        foreach (cmsPropertyData propData in properties)
                        {
                            SqlCommand propCommand = connection.CreateCommand();
                            propCommand.CommandText = string.Format("DELETE FROM cmsPropertyData WHERE versionId = '{0}'", version.VersionId);
                            propCommand.Transaction = transaction;
                            deletedProperties += propCommand.ExecuteNonQuery();
                        }
                        log.Info(string.Format("{0} properties for node {1} from version {2} deleted", deletedProperties, node.id, version.VersionDate.ToShortDateString()));
                        commandVersion.CommandText = string.Format("DELETE FROM cmsContentVersion WHERE versionId = '{0}'", version.VersionId);
                        commandVersion.Transaction = transaction;
                        int i1 = commandVersion.ExecuteNonQuery();
                        log.Info(string.Format("Version {1} is deleted for node {0}", node.id, i1));
                        commandDocument.CommandText = string.Format("DELETE FROM cmsDocument WHERE versionId = '{0}'", version.VersionId);
                        commandDocument.Transaction = transaction;
                        int i2 = commandDocument.ExecuteNonQuery();
                        log.Info(string.Format("Document is deleted for node {0}", node.id));
                        transaction.Commit();
                        //log.Info(string.Format("A version with {1} properties is cleaned for node {0}", node.id, deletedProperties));
                        connection.Close();
                    }
                }
                log.Info(string.Format("Node {0} cleaned", node.id));
                //System.GC.Collect();
            }

            Console.ReadKey();
        }
    }
}
